#!/usr/bin/perl
#
# Write the "short" report that lists Linux users
# by place of work, country and how long they have used Linux
#
# The country table also includes registered friends and machines
#

require "reportsub.pl";
use Counter;

#===================== DATA FETCH =======================

$places = Counter::openfile("places");
@countries = $places->select("type = 'country'");
$persons = Counter::openfile("persons");
%personcounts = $persons->counts("country");
$machines = Counter::openfile("machines");
%machinecounts = $machines->counts("country");
$friends = Counter::openfile("friends");
%friendcounts = $friends->counts("country");


# Calculate some other numbers
for $crec (@countries) {
    # Check the usercount to see if recount is needed
    if ($$crec{users} != $personcounts{"\L$$crec{name}"}) {
	my $ccount = $personcounts{"\L$$crec{name}"};
	warn "Recount $$crec{name}? count $ccount stored $$crec{users}\n";
    }
    if ($$crec{population} > 0) {
	$$crec{userdensity} = $$crec{users} / $$crec{population};
    } elsif ($$crec{users} > 0) {
	print STDERR "$$crec{name}: Users but no population\n";
    }
    $$crec{machines} = $machinecounts{"\L$$crec{name}"};
    $$crec{friends} = $friendcounts{"\L$$crec{name}"};
}

# Side effect (arg): store numbers into a table
$FileTable::dbhandle->do("DROP TABLE temp_short"); # ignore error
$FileTable::dbhandle->do("
CREATE TABLE temp_short (
  country char(2) PRIMARY KEY,
  name varchar(64),
  hostcount int DEFAULT 0,
  population float DEFAULT 0,
  userdensity float DEFAULT -1,
  users int DEFAULT 0,
  machines int DEFAULT 0,
  friends int DEFAULT 0,
  rank int
)") || die "CREATE TABLE temp_short failed\n";

$rank = 1;
$insertstm = $FileTable::dbhandle->prepare("INSERT temp_short VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
    || die "Error in prepare INSERT";
for $crec (sort {$b->{userdensity} <=> $a->{userdensity}} @countries) {
    $country = $crec->{name};
    $insertstm->execute($$crec{name},
			 $$crec{longname},
			 $$crec{hostcount},
			 $$crec{population},
			 $$crec{userdensity},
			 $$crec{users},
			 $$crec{machines},
			 $$crec{friends},
                         $rank
			 ) || die "Error in INSERT";
    ++ $rank;
}
    



$personcount = $persons->count();
$friendcount = $friends->count();
$machinecount = $machines->count();
$date = localtime;

# =============== Introductory remarks ==========================
$mincount = printwithcommas($personcount*20);
$maxcount = printwithcommas($personcount*500);
print <<EoF;
<?php include "counter2.php" ?>
<?php pagestart() ?>
<HTML>
<?php pagehead(_("Summary Report")) ?>

<body bgcolor="white">
<?php pagetop(_("Linux Counter Summary Report")) ?>
<?php print _("This is the Linux Counter summary as of") ?> $date
<p>
<?php print _("There are") ?> $personcount <?php print _("persons registered.") ?><br>
<?php print _("There are") ?> $machinecount <?php print _("machines registered.") ?><br>
<p>
<?php print _("I guesstimate that between 0.2% and 5% of all Linux users have registered with the Linux Counter. So the total number of Linux users is probably between") ?> $mincount <?php print _("and") ?> $maxcount <?php print _("people.") ?>
<h2><?php print _("The history of the Linux user count") ?></h2>
<img src="all-users.png">
<br>
<?php print _("The top curve shows the number of registered Linux users.") ?>
<br>
<?php print _("The bottom curve shows the number of &quot;active&quot; Linux users - those who have") ?> <a href="/login.php"><?php print _("visited") ?></a> <?php print _("their account on the counter in the last 2 years.") ?>
<br>
<?php print _("In November 2001, &quot;inactive&quot; users were") ?> <a href="/news/deletion.php"><?php print _("removed") ?></a> <?php print _("from the count; since January, the inactive users are removed continuously.") ?>
EoF

# =============== List countries by density =====================

# The following prints a PHP statement - the number of indirections can be a BIT perplexing...
print << 'EoF';
<?php
    if (!$orderby) { $orderby = "userdensity"; }
    if (!$limit) { $limit = 1; }
?>
<a name="table"></a>
<h2><?php print _("WHERE LINUX USERS LIVE") ?></h2>
<?php print _("The table is sorted by") . " "  ?>
<?php
    if ($orderby == "userdensity") {
	print _("number of users divided by population") . "\n";
    } else {
	print $orderby;
    }
   if ($limit > 1) {
       print _("Countries with less than $limit users are not listed") . "\n";
   }
?>
<table border=4>
<tr>
<th> # </th>
<th>&nbsp;<th align=left><?php print _("Country") ?></th>
<!-- note - RFC 2396 specifies that fragment follows query part -->
<th><a href="/reports/short.php?orderby=users#table"><?php print _("Pers") ?></a></th>
<th><a href="/reports/short.php?orderby=machines#table"><?php print _("Mach") ?></a></th>
<th><a href="/reports/short.php?orderby=userdensity#table"><?php print _("P/Mpop") ?></a></th>
<th><a href="/reports/short.php?orderby=population#table"><?php print _("People") ?></a></th>
</tr>
<?php
    
    $query = mysql_query("select * from temp_short where users >= $limit order by $orderby desc");

    if ($query == 0) {
       print "</table>" . _("MySQL error") . ": " . mysql_errno() . ":" . mysql_error();
       die("Query for count failed");
    }
    $rank = 1;
    while ($arr = mysql_fetch_array($query)) {
        print "<tr align=right>\n<td>$rank</td>\n";
        print "<td align=left>$arr[country]</td>\n";
        print "<td align=left><a href=\"/reports/place.php?place=$arr[country]\">$arr[name]</a></td>\n";
        printf("<td>%d</td>\n", $arr[users]);
        printf("<td>%d</td>\n", $arr[machines]);
        printf("<td>%5.2f</td>\n", $arr[userdensity]);
        if ($arr[population] >= 1) {
	    printf("<td>%6.1fM</td>\n", $arr[population]);
	} else {
	    printf("<td>%6.0fK</td>\n</tr>\n", $arr[population] * 1000);
	}
        ++ $rank;
    }
?>
EoF
print "</table>\n\n";

#==================== Table of users and places ==================
%usages = $persons->counts("usage");
for $id (keys(%usages)) {
    if ($id =~ /, /) {
	@newid = split(/, /, $id);
	for $id2 (@newid) {
	    $usages{$id2} += $usages{$id};
	}
	delete $usages{$id};
    }
}

print << 'EoF';
<?php print "<h2>" . _("WHERE PEOPLE USE LINUX") . "</h2>\n" ?>
<table border=4 cellpadding=2>
EoF

printf "\n<tr align=right><th align=left>%-10s</th><th>%5s</th><th>%5s</th></tr>\n", "<?php print _(\"Place\") ?>", "<?php print _(\"Users\") ?>", "<?php print _(\"Percent\") ?>";
for $usage (keys(%usages)) {
    printf "<tr align=right><td align=left>%-10s</td><td>%5d</td><td>%6.2f%%</td></tr>\n",
    $usage?"<?php print _(\"at $usage\") ?>":"<?php print _(\"not specified\") ?>", $usages{$usage},
    100 * $usages{$usage} / $personcount;
    $answercount += $usages{$usage};
}
print "</table>\n";

print <<EoF;
<p>
<?php print _("The total number of answers is") ?> $answercount <?php print _("for") ?> $personcount <?php print _("registered users.") ?>

<h2><?php print _("WHEN PEOPLE STARTED USING LINUX") ?></h2>
<img src="started.png">
<br>
<?php print _("This graph shows when people say they started to use Linux, and also the rate of registration with the Linux Counter.") ?>
<p>
<?php print _("Note: This is based on the") ?> $perssums{started}{total} <?php print _("people who answered this question, less the answers that were not computable or wildly wrong (such as starttimes before 1991 or after the current year).") ?>
<p>
<?php print _("The peaks in December occur because unknown month names are moved to December, in order to avoid giving an impression of too early adoption of Linux.") ?>
<p>
<?php print _("The two first large peaks in the registration rate probably correspond to the opening of the counter and the introduction of Web-based registration.") ?>
<p>
<?php print _("Among the implications one can read out of this graph is that it may take a while from the time someone starts using Linux until he registers with the counter.") ?>
<?php pagebottom() ?>
</body>
</html>
EoF
